const mysql = require('mysql')
const config = require('./mysql_config')

var pool = mysql.createPool({
    host: config.database.HOST,
    user: config.database.USERNAME,
    password: config.database.PASSWORD,
    database: config.database.DATABASE,
    port: config.database.PORT
})

let query = (sql, values) => {
    return new Promise((resolve, reject) => {
        pool.getConnection((err, connection) => {
            if (err) {
                reject(err)
            } else {
                connection.query(sql, values, (err, rows) => {
                    if (err) {
                        reject(err)
                    } else {
                        resolve(rows)
                    }
                    connection.release()
                })
            }
        })
    })
}

let products =
    `create table if not exists products(
    id INT NOT NULL AUTO_INCREMENT,
    title VARCHAR(50) NOT NULL,
    price FLOAT(10,2) NOT NULL,
    inventory INT NOT NULL,
    PRIMARY KEY ( id )
   );`

let createTable = (sql) => {
    return query(sql, [])
}

createTable(products)

let findAllProducts = () => {
    let _sql = ` select * FROM products;`
    return query(_sql)
}

let findProductById = (id) => {
    let _sql = ` select * FROM products where id = ${id} `
    return query(_sql)
}

let updateProducts = (values) => {
    let _sql = ` update products set inventory=? where id=? `
    return query(_sql, values)
}

module.exports = {
    findAllProducts,
    findProductById,
    updateProducts
}